package com.andrew.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.andrew.dao.BookDao;
import com.andrew.entity.Book;
import com.andrew.entity.CartDetail;
import com.andrew.util.JDBCUtils;


public class BookDaoImpl implements BookDao {
	public static int pageSize=12;//设置一页显示多少书
	private QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
	//分页查询
	@Override
	public List<Book> findBooksByPage(String queryTerm ,int currentPage) throws SQLException {
			
		//第一个问号，代表一页返回多少条记录  ， 第二个问号， 跳过前面的多少条记录。
		String sql="select * from Book where BookName LIKE ? and Stock>0 limit ? offset ?";
		queryTerm="%"+queryTerm+"%";
		return qr.query(sql, new BeanListHandler<Book>(Book.class),queryTerm,pageSize,(currentPage-1)*pageSize);
	}
	
	//查询所有书本
	@Override
	public List<Book> findAllExistBooks(String queryTerm) throws SQLException {
		
		//查询所有书本
		String sql="select * from Book where Stock>0 and BookName LIKE ?";
		queryTerm="%"+queryTerm+"%";
		return qr.query(sql,new BeanListHandler<Book>(Book.class),queryTerm);
	}

	//查询一共多少本书
	@Override
	public long getExistBooksCount(String queryTerm) throws SQLException {
		
		String sql="select count(*) from Book where Stock>0 and BookName LIKE ?";
		queryTerm="%"+queryTerm+"%";
		return qr.query(sql,new ScalarHandler<Long>(),queryTerm);
	}

	@Override
	public Book queryBookbyBookId(int bookId) throws SQLException {

		String sql="SELECT * FROM Book where BookId=?";
		return qr.query(sql, new BeanHandler<Book>(Book.class),bookId);
		
	}
	/***
	 *	添加图书 
	 *
	 */
	@Override
	public int addBook(Book book) throws SQLException {
		String sql="INSERT INTO Book VALUES(0,?,?,?,?,?,?)";
		return qr.update(sql,book.getBookName(),book.getPicture(),book.getAuthor(),book.getPrice(),book.getDescription(),book.getStock());
	}

	/**
	 * 改变库存
	 * stock状态：-1 删除 0:下架 1.上架
	 */
	@Override
	public int changeStocks(int bookId, int quantity) throws SQLException {
		String sql="update Book set Stock=? where BookId=?";
		return qr.update(sql,quantity,bookId);
	}

	//	public List<Book> queryAllBook()throws SQLException;
	@Override
	public List<Book> queryAllBook()throws SQLException {
		String sql="SELECT * FROM Book WHERE Stock>-1";
		return qr.query(sql, new BeanListHandler<Book>(Book.class));
	}

	@Override
	public int updatePictureForBook(int bookId, String picture) throws SQLException {
		String sql="Update Book Set Picture=? where BookId=?";
		return qr.update(sql,picture,bookId);
	}
	/***
	 * 修改图书的所有信息 除了照片
	 */
	@Override
	public int updateBookAll(Book book) throws SQLException {
		
		String sql="UPDATE Book SET BookName=?,price=?,author=?,description=?,stock=? where bookId=?";
		return qr.update(sql,book.getBookName(),book.getPrice(),book.getAuthor(),book.getDescription(),book.getStock(),book.getBookId());
	}
	
	

	

}
